﻿using System;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Web.UI.WebControls;

namespace WebBill
{
	public partial class Bill : System.Web.UI.Page
	{
		private string FormatStrPercent(string str)
		{
			return String.Format("{0:#0.00%}", float.Parse(str));
		}

		private string FormatStrNum(string str)
		{
			return String.Format("{0:#,##0.#####}", float.Parse(str));
		}

		private void SetProfit(string str, Label lb)
		{
			if (float.Parse(str) > 0)
			{
				lb.CssClass = "profit";
			}
			else
			{
				lb.CssClass = "lost";
			}
		}

		protected void Page_Load(object sender, EventArgs e)
		{
			DO.Common.Trace.SetLogFile(DO.Common.DreamOnConfig.logFolder, "Bill");

			string id = Request["id"];
			if (id == null) id = "C121114253";

			Title = DateTime.Now.Year + "_" + DateTime.Now.Month + "月份對帳單_" + id;


			//讀取文字檔
			using (StreamReader sr = new StreamReader(DO.Common.DreamOnConfig.billFolder + "ps.txt", System.Text.Encoding.Default))
			{
				txt_ps.Text = sr.ReadToEnd();
			}
			//讀取db

			//string connString = ConfigurationManager.ConnectionStrings["DreamOnConnStr"].ConnectionString;

			string connString = DO.Common.DreamOnConfig.connectionString_web;

			using (DO.Common.DataFunction DF = new DO.Common.DataFunction(connString))
			{
				DataSet ds = new DataSet();
				string SQLstr;

				#region 取出客戶資料 ========================================================
				SQLstr = "SELECT View_CUSTOMERS.*, COALESCE ( ABS(b.TAKE_MONEY), 0) AS TAKE_MONEY FROM View_CUSTOMERS LEFT OUTER JOIN (SELECT ID, SUM(TRADE_MONEY) AS TAKE_MONEY FROM View_TRADES WHERE (TRADE_MONEY < 0) GROUP BY   ID) AS b ON View_CUSTOMERS.ID = b.ID WHERE (View_CUSTOMERS.ID = '" + id + "')";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);

				Customer_Name.Text = ds.Tables[0].Rows[0]["NAME"].ToString();

				Customer_Cost.Text = FormatStrNum(ds.Tables[0].Rows[0]["TOTAL_COST"].ToString());
				Customer_Unit.Text = FormatStrNum(ds.Tables[0].Rows[0]["TOTAL_UNIT"].ToString());
				Customer_Asset.Text = FormatStrNum(ds.Tables[0].Rows[0]["ASSET_VALUE"].ToString());
				Customer_AvgCost.Text = FormatStrNum(ds.Tables[0].Rows[0]["AVG_COST"].ToString());

				Customer_ROI.Text = FormatStrPercent(ds.Tables[0].Rows[0]["ROI"].ToString());
				SetProfit(ds.Tables[0].Rows[0]["ROI"].ToString(), Customer_ROI);
				setGOChart(ds.Tables[0].Rows[0]["ROI"].ToString(), Customer_ROIchart);
				Customer_take.Text = FormatStrNum(ds.Tables[0].Rows[0]["TAKE_MONEY"].ToString());
				//損益
				string profit = (Int32.Parse(ds.Tables[0].Rows[0]["ASSET_VALUE"].ToString()) - Int32.Parse(ds.Tables[0].Rows[0]["TOTAL_COST"].ToString())).ToString();
				Customer_Profit.Text = FormatStrNum(profit);
				SetProfit(profit, Customer_Profit);

				float hisroi =
					//(float.Parse(profit) + float.Parse(ds.Tables[0].Rows[0]["TAKE_MONEY"].ToString()))/
				(float.Parse(profit)) /
					(float.Parse(ds.Tables[0].Rows[0]["TOTAL_COST"].ToString()) + float.Parse(ds.Tables[0].Rows[0]["TAKE_MONEY"].ToString()));

				Customer_HISROI.Text = FormatStrPercent(hisroi.ToString());
				SetProfit(hisroi.ToString(), Customer_HISROI);
				setGOChart(ds.Tables[0].Rows[0]["ROI"].ToString(), Customer_HISROIchart);

				if (ds.Tables[0].Rows[0]["TAKE_MONEY"].ToString().Equals("0"))
				{
					take_tr.Visible = false;
				}

				#endregion
				
				#region 取出客戶進出資料 ========================================================
				SQLstr = "SELECT CONVERT(varchar,TRADE_DATE,111) as DATE,TRADE_MONEY,NAV,UNIT FROM View_TRADES WHERE ID LIKE '" + id + "'  ORDER BY TRADE_DATE ASC";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);

				for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
				{
					TableRow tr = new TableRow();
					TableCell td = new TableCell();
					td.Text = ds.Tables[0].Rows[i]["DATE"].ToString();
					tr.Cells.Add(td);

					td = new TableCell();
					td.Text = FormatStrNum(ds.Tables[0].Rows[i]["TRADE_MONEY"].ToString());
					tr.Cells.Add(td);

					td = new TableCell();
					td.Text = FormatStrNum(ds.Tables[0].Rows[i]["NAV"].ToString());
					tr.Cells.Add(td);

					td = new TableCell();
					td.Text = ds.Tables[0].Rows[i]["UNIT"].ToString();
					tr.Cells.Add(td);

					Customer_TradeHis.Rows.Add(tr);
				}

				#endregion
				
				#region 取出基金目前資料 ========================================================

				SQLstr = "SELECT TOP (1) *,dbo.getROI({ fn NOW()}) as ROI FROM View_ASSET ORDER BY  ASSET_DATE DESC";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);

				Total_Asset.Text = FormatStrNum(ds.Tables[0].Rows[0]["ASSET_MONEY"].ToString());
				NAV.Text = FormatStrNum(ds.Tables[0].Rows[0]["NAV"].ToString());
				//Customer_NAV.Text = FormatStrNum(ds.Tables[0].Rows[0]["NAV"].ToString());
				Total_ROI.Text = FormatStrPercent(ds.Tables[0].Rows[0]["ROI"].ToString());
				SetProfit(ds.Tables[0].Rows[0]["ROI"].ToString(), Total_ROI);
				setGOChart(ds.Tables[0].Rows[0]["ROI"].ToString(), Total_ROIchart);
				upddate.Text = ds.Tables[0].Rows[0]["ASSET_DATE"].ToString().Remove(ds.Tables[0].Rows[0]["ASSET_DATE"].ToString().IndexOf(" "));

				#endregion

				#region 取出基金歷史資料 ========================================================

				SQLstr = "SELECT "
					+ "dbo.getROI({ fn NOW() }) AS ROI_NOW"
					+ ", dbo.getPartROI({ fn NOW() }, DATEADD(month, - 1, { fn NOW() })) AS ROI_1MONTH"
					+ ", dbo.getPartROI({ fn NOW() }, DATEADD(month, - 3, { fn NOW() })) AS ROI_3MONTH"
					+ ", dbo.getPartROI({ fn NOW() }, DATEADD(month, - 6, { fn NOW() })) AS ROI_6MONTH"
					+ ", dbo.getPartROI({ fn NOW() }, DATEADD(year, - 1, { fn NOW() })) AS ROI_1YEAR";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);

				ROI_1m.Text = FormatStrPercent(ds.Tables[0].Rows[0]["ROI_1MONTH"].ToString());
				SetProfit(ds.Tables[0].Rows[0]["ROI_1MONTH"].ToString(), ROI_1m);
				setGOChart(ds.Tables[0].Rows[0]["ROI_1MONTH"].ToString(), imggROI_1m);
				ROI_3m.Text = FormatStrPercent(ds.Tables[0].Rows[0]["ROI_3MONTH"].ToString());
				SetProfit(ds.Tables[0].Rows[0]["ROI_3MONTH"].ToString(), ROI_3m);
				setGOChart(ds.Tables[0].Rows[0]["ROI_3MONTH"].ToString(), imggROI_3m);
				ROI_6m.Text = FormatStrPercent(ds.Tables[0].Rows[0]["ROI_6MONTH"].ToString());
				SetProfit(ds.Tables[0].Rows[0]["ROI_6MONTH"].ToString(), ROI_6m);
				setGOChart(ds.Tables[0].Rows[0]["ROI_6MONTH"].ToString(), imggROI_6m);
				ROI_1y.Text = FormatStrPercent(ds.Tables[0].Rows[0]["ROI_1YEAR"].ToString());
				SetProfit(ds.Tables[0].Rows[0]["ROI_1YEAR"].ToString(), ROI_1y);
				setGOChart(ds.Tables[0].Rows[0]["ROI_1YEAR"].ToString(), imggROI_1y);

				#endregion

				#region 取出基金歷史走勢 ========================================================

				SQLstr = "select CONVERT(varchar(12),ASSET_DATE,111) as ASSET_DATE,NAV from dbo.getHisAsset(DATEADD(month, - 1, { fn NOW() }))";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);
				setLineCharts(ds.Tables[0].Rows, imgROI_1m, "最近一月走勢圖");

				SQLstr = "select CONVERT(varchar(12),ASSET_DATE,111) as ASSET_DATE,NAV from dbo.getHisAsset(DATEADD(month, - 3, { fn NOW() }))";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);
				setLineCharts(ds.Tables[0].Rows, imgROI_3m, "最近三月走勢圖");

				SQLstr = "select CONVERT(varchar(12),ASSET_DATE,111) as ASSET_DATE,NAV from dbo.getHisAsset(DATEADD(month, - 6, { fn NOW() }))";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);
				setLineCharts(ds.Tables[0].Rows, imgROI_6m, "最近六月走勢圖");

				SQLstr = "select CONVERT(varchar(12),ASSET_DATE,111) as ASSET_DATE,NAV from dbo.getHisAsset(DATEADD(year, - 1, { fn NOW() }))";
				//DO.Common.Trace.DebugWrite("Bill", SQLstr);
				DF.RunSqlCommand(SQLstr, out ds);
				setLineCharts(ds.Tables[0].Rows, imgROI_1y, "最近一年走勢圖");

				#endregion
			}
		}

		private void setGOChart(string ra, Image iim)
		{
			float rr = float.Parse(ra);
			if (!Regex.IsMatch(ra, @"^\d*$")) rr = rr * 100;
			rr = rr / 2 + 50;
			iim.ImageUrl = "https://chart.googleapis.com/chart?chs=40x17&cht=gom&chma=0,0,0,0|0,0&chd=t:" + rr.ToString() + "&chco=008000,00FF00,FFFF00,FF8040,FF0000,AA0000";
		}

		private void setLineCharts(DataRowCollection tbrow, Image iim, string title)
		{
			/**/
			//-------常數區--------
			const string width = "500";
			const string height = "210";
			const float limit = 0.15f;
			const float Xcount = 4f;
			const float Xrange = 100 / Xcount / 2;
			/**/
			//---------------------
			int[] iarr = new int[tbrow.Count];

			string gchart = "cht=lc";	//折線圖
			gchart += "&chs=" + width + "x" + height;	//圖表大小
			gchart += "&chtt=" + title.Replace(" ", "+");
			gchart += "&chts=222222,13";
			//gchart += "&chco=FF9900,D4D1FF";


			int mmax = -99;
			int mmin = 999999;

			//填入數值
			gchart += "&chd=t:";
			for (int i = 0; i < tbrow.Count; i++)
			{
				iarr[i] = Convert.ToInt32(float.Parse(tbrow[i]["NAV"].ToString()));
				mmax = Math.Max(mmax, iarr[i]);
				mmin = Math.Min(mmin, iarr[i]);

				gchart += iarr[i].ToString();
				if (i != tbrow.Count - 1)
					gchart += ",";
			}

			/*	期貨指數
			 ------------------------------------------------------*/
			//gchart += "|";
			//int fmax = 0;
			//int fmin = 20000;
			//for (int i = 0; i < tbrow.Count; i++)
			//{
			//    iarr[i] = Convert.ToInt32(float.Parse(tbrow[i]["FUTURE_VALUE"].ToString()));
			//    fmax = Math.Max(fmax, iarr[i]);
			//    fmin = Math.Min(fmin, iarr[i]);
			//    gchart += iarr[i].ToString();
			//    if (i != tbrow.Count - 1)
			//        gchart += ",";
			//}
			/*
			 ------------------------------------------------------*/

			//設定資料上下限
			int bottom = Convert.ToInt32(mmin - ((mmax - mmin) * limit));
			int top = Convert.ToInt32(mmax + ((mmax - mmin) * limit));

			//int fbottom = Convert.ToInt32(fmin - ((fmax - fmin) * limit));
			//int ftop = Convert.ToInt32(fmax + ((fmax - fmin) * limit));

			gchart = gchart + "&chds=" + bottom + "," + top;// +"," + fbottom + "," + ftop;

			//設定顯示的軸線
			gchart += "&chxt=r,x";

			//-------------設定r軸----------------------------
			//設定r軸上下邊界
			gchart += "&chxr=0," + bottom + "," + top;
			gchart += "&chxs=0,313131,12,-1,tl,C0C0C0|1,313131,12";
			//-------------設定x軸----------------------------
			//string chxp = "&chxp=1";	//x軸位置
			gchart += "&chxl=1:";	//x軸(日期)
			for (int i = 0; i <= Xcount - 1; i++)
			{
				int j = Convert.ToInt32(Math.Round((tbrow.Count - 1) / Xcount * i));
				gchart += "|" + tbrow[j]["ASSET_DATE"].ToString();
				//chxp += "," + Convert.ToInt32(Math.Round((double)(j+1) / (tbrow.Count),2)*100).ToString();
			}
			gchart += "|" + tbrow[tbrow.Count - 1]["ASSET_DATE"].ToString();
			//gchart += chxp + ",100";
			//設定x軸格線
			gchart += "&chg=" + Xrange + ",0,2,2," + Xrange + ",0";	//次格線(虛)
			gchart += "&chxtc=0,-" + width;	//主格線(實)


			iim.ImageUrl = "https://chart.googleapis.com/chart?" + gchart;
		}
	}
}